package Database;

import java.util.ArrayList;

import model.Friend;
import Utils.Common;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.readystatesoftware.sqliteasset.SQLiteAssetHelper;

public class MyDatabase extends SQLiteAssetHelper {
    private static final int DATABASE_VERSION = 1;

    public MyDatabase(Context context) {
	super(context, Common.DATABASE_NAME, null, DATABASE_VERSION);
    }

    public ArrayList<String> getListFriend() {
	ArrayList<String> retList = new ArrayList<String>();
	SQLiteDatabase db = getReadableDatabase();
	// @f:off
	String sql = ""
		+ "SELECT friend_id,"
		+ "       friend_name,"
		+ "       a.group_id,"
		+ "       last_seen,"
		+ "       event_seen,"
		+ "       julianday('now')-julianday(last_seen) AS pass_day,"
		+ "       nof_day < (julianday('now')-julianday(last_seen)) AS is_lost,"
		+ "       '('||friend_id||')-' || phone_id || '-' || friend_name || ' -- ' ||  ROUND(julianday('now')-julianday(last_seen)) || ' ['||(nof_day < (julianday('now')-julianday(last_seen)))||']' as friend_display"
		+ " FROM friend a"
		+ " 	LEFT JOIN groups b"
		+ " WHERE a.group_id = b.group_id"
		+ " ORDER BY pass_day DESC";
    	// @f:on
	Cursor c = db.rawQuery(sql, null);

	while (c.moveToNext()) {
	    Friend f = new Friend();
	    f.friend_id = c.getString(0);
	    f.friend_name = c.getString(1);
	    f.group_id = c.getString(2);
	    f.last_seen = c.getString(3);
	    f.event_seen = c.getString(4);
	    f.pass_day = c.getString(5);
	    f.is_lost = c.getString(6);
	    f.friend_display = c.getString(7);
	    retList.add(f.friend_display);
	}

	return retList;
    }

    public void updateSeenFriend(int friend_id) {
	SQLiteDatabase db = getReadableDatabase();
	String sql = "UPDATE friend" + " SET last_seen = datetime('now')" + " WHERE friend_id=" + friend_id;
	db.execSQL(sql);
    }

    public void deleteFriend(int friend_id) {
	SQLiteDatabase db = getReadableDatabase();
	String sql = "DELETE FROM friend" + " WHERE friend_id=" + friend_id;
	db.execSQL(sql);
    }

    public void addFriend(String friend_name, String phone_id) {
	SQLiteDatabase db = getReadableDatabase();
	Cursor c = db.rawQuery("SELECT 1 FROM friend WHERE phone_id=" + phone_id, null);
	String sql = null;
	if (c.getCount() == 0) {
	    sql = "INSERT INTO friend (friend_name, phone_id) VALUES ('" + friend_name + "', '" + phone_id + "')";
	} else {
	    sql = "UPDATE friend SET friend_name='" + friend_name + "' WHERE phone_id='" + phone_id + "'";
	}
	db.execSQL(sql);
    }
}